package ase.assignment.sts.db.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ase.assignment.sts.api.PortfolioHandler;
import ase.assignment.sts.beans.PortfolioEntity;
import ase.assignment.sts.db.DBHandler;

public class PortfolioHandlerImpl implements PortfolioHandler {

	PortfolioHandlerImpl() {

	}

	public void create(PortfolioEntity entity) {
		String sql = "insert into Portfolios ( id, name, customer_name ) values("
				+ entity.getId()
				+ ", '"
				+ entity.getName()
				+ "', '"
				+ entity.getCustomerName() + "')";
		try {
			System.out.println(sql);
			DBHandler.getInstance().createStatement().executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHandler.getInstance().dispose();
		}
	}

	public void delete(int porId) {
		String sql = "delete from Portfolios where id = " + porId;
		try {
			System.out.println(sql);
			DBHandler.getInstance().createStatement().executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHandler.getInstance().dispose();
		}
	}

	public void update(PortfolioEntity entity) {
		String sql = "update Portfolios set name='" + entity.getName()
				+ "', customer_name='" + entity.getCustomerName()
				+ "' where id=" + entity.getId();
		try {
			System.out.println(sql);
			DBHandler.getInstance().createStatement().executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHandler.getInstance().dispose();
		}
	}

	public PortfolioEntity findById(int porId) {
		String sql = "select name, customer_name from Portfolios where id="
				+ porId;
		try {
			System.out.println(sql);
			ResultSet rs = DBHandler.getInstance().createStatement()
					.executeQuery(sql);
			if (rs != null && rs.next()) {
				PortfolioEntity entity = new PortfolioEntity(porId);
				entity.setName(rs.getString(1).trim());
				entity.setCustomerName(rs.getString(2).trim());
				return entity;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHandler.getInstance().dispose();
		}
		return null;
	}

	public PortfolioEntity[] getAllPortfolios(String username) {
		String sql = "select id, name from Portfolios where customer_name='"
				+ username + "'";
		try {
			System.out.println(sql);
			ResultSet rs = DBHandler.getInstance().createStatement()
					.executeQuery(sql);
			if (rs != null) {
				List<PortfolioEntity> list = new ArrayList<PortfolioEntity>();
				while (rs.next()) {
					PortfolioEntity entity = new PortfolioEntity(rs.getInt(1));
					entity.setName(rs.getString(2).trim());
					entity.setCustomerName(username);
					list.add(entity);
				}
				return list.toArray(new PortfolioEntity[list.size()]);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHandler.getInstance().dispose();
		}
		return null;
	}
}
